﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.sys;
using Domain.Entity.zct;
using Domain.IRepository;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Linq;

namespace EFCore.Repository
{
    public class SupplierRepository : ISupplierRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        public async Task<int> AddSupplier(Supplier supplier)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                INSERT INTO bs_supplier (
                    sup_code, sup_name, sup_address, sup_linkman, sup_linkphone,
                    sup_email, sup_industry, sup_type, sup_depId, sup_depName,
                    sup_userId, sup_userName, sup_status, sup_accountName,
                    sup_taxpayerNum, sup_bankAccount, sup_openBank, sup_openBankAddress,
                    create_name, create_time, is_delete, remark
                ) VALUES (
                    @sup_code, @sup_name, @sup_address, @sup_linkman, @sup_linkphone,
                    @sup_email, @sup_industry, @sup_type, @sup_depId, @sup_depName,
                    @sup_userId, @sup_userName, @sup_status, @sup_accountName,
                    @sup_taxpayerNum, @sup_bankAccount, @sup_openBank, @sup_openBankAddress,
                    @create_name, GETDATE(), 0, @remark
                );";

                var parameters = new DynamicParameters();
                parameters.Add("@sup_code", supplier.sup_code);
                parameters.Add("@sup_name", supplier.sup_name);
                parameters.Add("@sup_address", supplier.sup_address);
                parameters.Add("@sup_linkman", supplier.sup_linkman);
                parameters.Add("@sup_linkphone", supplier.sup_linkphone);
                parameters.Add("@sup_email", supplier.sup_email);
                parameters.Add("@sup_industry", supplier.sup_industry);
                parameters.Add("@sup_type", supplier.sup_type);
                parameters.Add("@sup_depId", supplier.sup_depId);
                parameters.Add("@sup_depName", supplier.sup_depName);
                parameters.Add("@sup_userId", supplier.sup_userId);
                parameters.Add("@sup_userName", supplier.sup_userName);
                parameters.Add("@sup_status", supplier.sup_status);
                parameters.Add("@sup_accountName", supplier.sup_accountName);
                parameters.Add("@sup_taxpayerNum", supplier.sup_taxpayerNum);
                parameters.Add("@sup_bankAccount", supplier.sup_bankAccount);
                parameters.Add("@sup_openBank", supplier.sup_openBank);
                parameters.Add("@sup_openBankAddress", supplier.sup_openBankAddress);
                parameters.Add("@create_name", supplier.create_name);
                parameters.Add("@remark", supplier.remark);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        public async Task<int> DeleteSupplier(int id)
        {
            using (var sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 1. 检查供应商是否存在且未被删除
                var checkExistsSql = "SELECT COUNT(1) FROM bs_supplier WHERE sup_id = @id AND is_delete = 0";
                var exists = await sqlConnection.QuerySingleAsync<int>(checkExistsSql, new { id });
                if (exists == 0)
                {
                    return -1; // 供应商不存在或已被逻辑删除
                }

                // 2. 检查是否有未删除的引用记录
                var checkSql = @"
                -- 检查进货订单
                SELECT COUNT(*) FROM ord_purchase_h WHERE sup_id = @id AND is_delete = 0
                UNION ALL
                -- 检查进货退货单
                SELECT COUNT(*) FROM ord_purchase_return_h WHERE sup_id = @id AND is_delete = 0
                UNION ALL
                -- 检查入库单
                SELECT COUNT(*) FROM in_instock_h WHERE sup_id = @id AND is_delete = 0";

                var counts = await sqlConnection.QueryAsync<int>(checkSql, new { id });

                // 如果任何一个表有引用记录，都不能删除
                if (counts.Any(count => count > 0))
                {
                    return 0; // 有引用，删除失败
                }

                // 3. 没有引用，执行逻辑删除
                var updateSql = @"
                UPDATE bs_supplier 
                SET is_delete = 1 
                WHERE sup_id = @id AND is_delete = 0"; // 再次确认未被删除，防止并发问题

                var rowsAffected = await sqlConnection.ExecuteAsync(updateSql, new { id });
                // 理论上 rowsAffected 应该是 1，但如果并发情况下已被其他请求删除，则可能是 0
                return rowsAffected > 0 ? 1 : -1; // 1=成功, -1=意外失败(如并发删除)
            }
        }

        public async Task<int> UpdateSupplier(Supplier supplier)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                UPDATE bs_supplier
                SET 
                    sup_name = @sup_name,
                    sup_address = @sup_address,
                    sup_linkman = @sup_linkman,
                    sup_linkphone = @sup_linkphone,
                    sup_email = @sup_email,
                    sup_industry = @sup_industry,
                    sup_type = @sup_type,
                    sup_depId = @sup_depId,
                    sup_depName = @sup_depName,
                    sup_userId = @sup_userId,
                    sup_userName = @sup_userName,
                    sup_status = @sup_status,
                    sup_accountName = @sup_accountName,
                    sup_taxpayerNum = @sup_taxpayerNum,
                    sup_bankAccount = @sup_bankAccount,
                    sup_openBank = @sup_openBank,
                    sup_openBankAddress = @sup_openBankAddress,
                    update_name = @update_name,
                    update_time = GETDATE(),
                    remark = @remark
                WHERE sup_id = @sup_id;";

                var parameters = new DynamicParameters();
                parameters.Add("@sup_name", supplier.sup_name);
                parameters.Add("@sup_address", supplier.sup_address);
                parameters.Add("@sup_linkman", supplier.sup_linkman);
                parameters.Add("@sup_linkphone", supplier.sup_linkphone);
                parameters.Add("@sup_email", supplier.sup_email);
                parameters.Add("@sup_industry", supplier.sup_industry);
                parameters.Add("@sup_type", supplier.sup_type);
                parameters.Add("@sup_depId", supplier.sup_depId);
                parameters.Add("@sup_depName", supplier.sup_depName);
                parameters.Add("@sup_userId", supplier.sup_userId);
                parameters.Add("@sup_userName", supplier.sup_userName);
                parameters.Add("@sup_status", supplier.sup_status);
                parameters.Add("@sup_accountName", supplier.sup_accountName);
                parameters.Add("@sup_taxpayerNum", supplier.sup_taxpayerNum);
                parameters.Add("@sup_bankAccount", supplier.sup_bankAccount);
                parameters.Add("@sup_openBank", supplier.sup_openBank);
                parameters.Add("@sup_openBankAddress", supplier.sup_openBankAddress);
                parameters.Add("@update_name", supplier.update_name);
                parameters.Add("@remark", supplier.remark);
                parameters.Add("@sup_id", supplier.sup_id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        //修改状态
        public async Task<int> UpdateStatus(int id, int sup_status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var sql = @"
                UPDATE bs_supplier
                SET 
                    sup_status = @sup_status
                WHERE sup_id = @sup_id;";
                if (1 == sup_status)
                    sup_status = 0;
                else
                    sup_status = 1;

                var parameters = new DynamicParameters();
                parameters.Add("@sup_status", sup_status);
                parameters.Add("@sup_id", id);

                return await sqlConnection.ExecuteAsync(sql, parameters);
            }
        }

        public async Task<FenYe<Supplier>> GetSupplier(FenYe<Supplier> fenYe, string? sup_code, string? sup_name, int? sup_status, string? sup_industry, string? sup_userName)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                string whereBuilder = $" is_delete=0 ";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(sup_code))
                {
                    whereBuilder += $" and (sup_code like '%{sup_code}%' or sup_name like '%{sup_name}%') ";
                }
                if (sup_status.HasValue)
                {
                    // 如果 sup_status 是 0 或 1，则添加相应的查询条件
                    whereBuilder += $" and sup_status = {sup_status}";
                }
                if (!string.IsNullOrEmpty(sup_industry))
                {
                    whereBuilder += $" and sup_industry like '%{sup_industry}%'";
                }
                if (!string.IsNullOrEmpty(sup_userName))
                {
                    whereBuilder += $" and sup_userName like '%{sup_userName}%'";
                }

                // 分页参数
                parameters.Add("@pageSize", 10);
                parameters.Add("@keyName", "sup_id"); // 主键字段
                parameters.Add("@tabelName", "bs_supplier");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder);
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Supplier>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        public async Task<List<Department>> GetDepartment(int? Department_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                StringBuilder whereBuilder = new StringBuilder("is_delete=0 AND department_status=0");
                var parameters = new DynamicParameters();

                if (Department_id.HasValue)
                {
                    whereBuilder.Append(" AND department_id = @Department_id");
                    parameters.Add("@Department_id", Department_id.Value);
                }

                string sql = $"SELECT * FROM zct_department WHERE {whereBuilder.ToString()}";
                return (await sqlConnection.QueryAsync<Department>(sql, parameters)).AsList();
            }
        }

        public async Task<List<User>> GetUser(int? Department_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询条件
                StringBuilder whereBuilder = new StringBuilder("is_delete=0 AND user_status=0");
                var parameters = new DynamicParameters();

                if (Department_id.HasValue)
                {
                    whereBuilder.Append(" AND department_id = @Department_id");
                    parameters.Add("@Department_id", Department_id.Value);
                }

                string sql = $"SELECT * FROM zct_user WHERE {whereBuilder.ToString()}";
                return (await sqlConnection.QueryAsync<User>(sql, parameters)).AsList();
            }
        }

        public async Task<List<DictData>> GetDictData(int dict_typename)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询语句，直接包含必要的条件
                string sql = "SELECT * FROM sys_dict_data WHERE is_delete = 0 AND status = 0 AND dict_id = @dict_typename";

                // 创建动态参数
                var parameters = new DynamicParameters();
                parameters.Add("@dict_typename", dict_typename);

                // 执行查询并返回结果集合
                return (await sqlConnection.QueryAsync<DictData>(sql, parameters)).AsList();
            }
        }

        public async Task<Supplier> GetIdSupplier(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建查询语句，直接包含必要的条件
                string sql = "SELECT * FROM bs_supplier WHERE  sup_id = @id";

                // 创建动态参数
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                // 执行查询并返回结果集合
                return await sqlConnection.QueryFirstOrDefaultAsync<Supplier>(sql, parameters);
            }
        }

        //高级查询
        public async Task<FenYe<Supplier>> GetSeniorSupplier(FenYe<Supplier> fenYe, string? sup_code, string? sup_name, string? sup_industry, string? sup_type, string? sup_linkman, string? sup_linkphone, string? sup_email, string? sup_depName, string? sup_userName, int? sup_status, string? sup_address, string? sup_accountName, string? sup_taxpayerNum, string? sup_bankAccount, string? sup_openBank, string? create_name, string? startTime, string? endTime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 构建基础查询条件
                StringBuilder whereBuilder = new StringBuilder("is_delete=0");

                // 转义所有字符串参数（防止SQL注入）
                if (!string.IsNullOrEmpty(sup_code))
                    whereBuilder.Append($" AND sup_code LIKE '%{EscapeSql(sup_code)}%'");

                if (!string.IsNullOrEmpty(sup_name))
                    whereBuilder.Append($" AND sup_name LIKE '%{EscapeSql(sup_name)}%'");

                if (!string.IsNullOrEmpty(sup_industry))
                    whereBuilder.Append($" AND sup_industry LIKE '%{EscapeSql(sup_industry)}%'");

                if (!string.IsNullOrEmpty(sup_type))
                    whereBuilder.Append($" AND sup_type LIKE '%{EscapeSql(sup_type)}%'");

                if (!string.IsNullOrEmpty(sup_linkman))
                    whereBuilder.Append($" AND sup_linkman LIKE '%{EscapeSql(sup_linkman)}%'");

                if (!string.IsNullOrEmpty(sup_linkphone))
                    whereBuilder.Append($" AND sup_linkphone LIKE '%{EscapeSql(sup_linkphone)}%'");

                if (!string.IsNullOrEmpty(sup_email))
                    whereBuilder.Append($" AND sup_email LIKE '%{EscapeSql(sup_email)}%'");

                if (!string.IsNullOrEmpty(sup_address))
                    whereBuilder.Append($" AND sup_address LIKE '%{EscapeSql(sup_address)}%'");

                if (!string.IsNullOrEmpty(sup_accountName))
                    whereBuilder.Append($" AND sup_accountName LIKE '%{EscapeSql(sup_accountName)}%'");

                if (!string.IsNullOrEmpty(sup_taxpayerNum))
                    whereBuilder.Append($" AND sup_taxpayerNum LIKE '%{EscapeSql(sup_taxpayerNum)}%'");

                if (!string.IsNullOrEmpty(sup_bankAccount))
                    whereBuilder.Append($" AND sup_bankAccount LIKE '%{EscapeSql(sup_bankAccount)}%'");

                if (!string.IsNullOrEmpty(sup_openBank))
                    whereBuilder.Append($" AND sup_openBank LIKE '%{EscapeSql(sup_openBank)}%'");

                if (!string.IsNullOrEmpty(create_name))
                    whereBuilder.Append($" AND create_name LIKE '%{EscapeSql(create_name)}%'");

                // 处理整型参数
                if (!string.IsNullOrEmpty(sup_depName))
                    whereBuilder.Append($" AND sup_depName LIKE '%{EscapeSql(sup_depName)}%'");

                if (!string.IsNullOrEmpty(sup_userName))
                    whereBuilder.Append($" AND sup_userName LIKE '%{EscapeSql(sup_userName)}%'");


                if (sup_status.HasValue)
                {
                    // 如果 sup_status 是 0 或 1，则添加相应的查询条件
                    whereBuilder.Append($" AND sup_status = {sup_status}");
                }

                // 处理创建时间范围
                if (!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    // 转义并清理输入
                    string start = EscapeSql(startTime.Trim());
                    string end = EscapeSql(endTime.Trim());

                    // 确保日期格式正确（取前10个字符作为日期部分）
                    string startDate = start.Length >= 10 ? start.Substring(0, 10) : start;
                    string endDate = end.Length >= 10 ? end.Substring(0, 10) : end;

                    // 构建包含全天的时间范围
                    whereBuilder.Append($" AND create_time BETWEEN '{startDate} 00:00:00' AND '{endDate} 23:59:59'");
                }

                // 分页参数
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", 10); // 默认10条
                parameters.Add("@keyName", "sup_id");
                parameters.Add("@tabelName", "bs_supplier");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@where", whereBuilder.ToString());
                parameters.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);

                // 执行存储过程
                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                var result = await sqlConnection.QueryAsync<Supplier>(sql, parameters);

                // 设置分页结果
                fenYe.List = result.ToList();
                fenYe.Rows = parameters.Get<int>("@rows");
                return fenYe;
            }
        }

        // SQL 注入防护：转义特殊字符
        private string EscapeSql(string value)
        {
            return value
                .Replace("'", "''")    // 转义单引号
                .Replace("--", "")      // 移除SQL注释
                .Replace(";", "");      // 移除语句分隔符
        }

        public async Task<List<string>> GetSupplierID()
        {
            await using (var sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                // 修正表名为 bs_supplier
                const string query = "SELECT sup_code FROM bs_supplier WHERE sup_code IS NOT NULL AND is_delete = 0"; // 建议加上 is_delete = 0

                // 使用Dapper的异步查询直接映射到string列表
                var result = (await sqlConnection.QueryAsync<string>(query)).AsList();

                return result;
            }
        }


        //查询所有供应商
        public async Task<List<Supplier>> GetAllSupplier()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select sup_id,sup_name from [dbo].[bs_supplier] where is_delete=0";
                return sqlConnection.Query<Supplier>(sql).ToList();
            }
        }
    }
}
